package com.hd.core.project.module.utils;

import com.hd.core.common.utils.poi.ExcelUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@SuppressWarnings("all")
public class ExcelUtilsWrapper extends ExcelUtil {

	private static final String EXTENSION_XLS = "xls";

	private static final String EXTENSION_XLSX = "xlsx";

	protected static final DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public ExcelUtilsWrapper(Class clazz) {
		super(clazz);
	}

	/***
	 * <pre>
	 * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
	 *   xls:HSSFWorkbook
	 *   xlsx：XSSFWorkbook
	 * &#64;param file excel文件File对象
	 * </pre>
	 */
	public static Workbook getWorkbook(File file) throws IOException, InvalidFormatException {
		Workbook workbook = null;
		if (file.getName().endsWith(EXTENSION_XLS)) {
			workbook = new HSSFWorkbook(new FileInputStream(file));
		} else if (file.getName().endsWith(EXTENSION_XLSX)) {
			workbook = new XSSFWorkbook(OPCPackage.open(file));
		}
		return workbook;
	}

	/**
	 * 取单元格的值
	 * 
	 * @param cell       单元格对象
	 * @param treatAsStr 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
	 * @return
	 */
	public static String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {
			return null;
		}
		if (treatAsStr) {
			// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
			// 加上下面这句，临时把它当做文本来读取
			cell.setCellType(CellType.STRING);
		}
		if (cell.getCellTypeEnum().getCode() == CellType.BOOLEAN.getCode()) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellTypeEnum().getCode() == CellType.NUMERIC.getCode()) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

	/**
	 * 获取指定单元格的值
	 *
	 * @param rowNumber  行数，从1开始
	 * @param cellNumber 列数，从1开始
	 * @return 该单元格的值
	 */
	public static String getCellValue(Workbook workbook, int sheetIndex, int rowNumber, int cellNumber) {
		String result;
		checkRowAndCell(rowNumber, cellNumber);
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		Row row = sheet.getRow(--rowNumber);
		Cell cell = row.getCell(--cellNumber);
		switch (cell.getCellTypeEnum()) {
		case BLANK:
			result = cell.getStringCellValue();
			break;
		case BOOLEAN:
			result = String.valueOf(cell.getBooleanCellValue());
			break;
		case ERROR:
			result = String.valueOf(cell.getErrorCellValue());
			break;
		case FORMULA:
			result = cell.getCellFormula();
			break;
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				result = format.format(cell.getDateCellValue());
			} else {
				result = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case STRING:
			result = cell.getRichStringCellValue().getString();
			break;
		default:
			result = cell.getStringCellValue();
			break;
		}
		return result;
	}

	/**
	 * 检查传进来的Row和Cell值是否大于1
	 * 
	 * @param rowNumber
	 * @param cellNumber
	 */
	public static void checkRowAndCell(int rowNumber, int cellNumber) {
		if (rowNumber < 1) {
			throw new RuntimeException("rowNumber less than 1");
		}
		if (cellNumber < 1) {
			throw new RuntimeException("cellNumber less than 1");
		}
	}

	public static String getTopic(Cell cell) {
		Comment cellComment = null;
		RichTextString richTextString = null;
		if (cell != null) {
			cellComment = cell.getCellComment();
			if (cellComment != null) {
				richTextString = cellComment.getString();
				if (richTextString != null)
					return richTextString.getString();
			}
		}
		return "";
	}

	/**
	 * 解析Excel中的文件标题-默认已序号两个字做标识
	 * 
	 * @param sheet
	 * @return
	 */
	public static List<Map<String, String>> parseExcelTitle(Sheet sheet) {
		return parseExcelTitle(sheet, "序号");
	}

	/**
	 * 解析Excel中的文件标题
	 * 
	 * @param sheet
	 * @param titleFlag 标题行第一个单元格的值
	 * @return 每一个Map就代表一个单元格的值和批注
	 */
	public static List<Map<String, String>> parseExcelTitle(Sheet sheet, String titleFlag) {
		Row currentRow = null;
		Cell currentCell = null;
		Cell currentRowFirstCell = null;
		Iterator<Cell> cellIterator = null;
		Map<String, String> valueAndTopic = null;
		Iterator<Row> rowIterator = sheet.rowIterator();
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();

		while (rowIterator.hasNext()) {
			currentRow = rowIterator.next();
			currentRowFirstCell = currentRow.getCell(0);
			if (getCellValue(currentRowFirstCell, false).equals(titleFlag)) {
				cellIterator = currentRow.cellIterator();
				while (cellIterator.hasNext()) {
					currentCell = cellIterator.next();
					valueAndTopic = new LinkedHashMap<String, String>();
					valueAndTopic.put(getCellValue(currentCell, true), getTopic(currentCell));
					result.add(valueAndTopic);
				}
				// 当执行到标题行后就结束循环体
				break;
			}
		}
		// 调用一下垃圾回收机制
		System.gc();
		return result;
	}
}
